CREATE
OR REPLACE PROCEDURE supply_other_info_proj (pphid IN NUMBER, xmlsource IN VARCHAR2) AUTHID CURRENT_USER IS icount INTEGER;

e_invalid_xml EXCEPTION;

BEGIN
-- 检查输入参数  
IF pphid IS NULL
OR pphid = 0 THEN RETURN;

END IF;

IF xmlsource IS NULL
OR xmlsource = '' THEN RETURN;

END IF;

-- 新增行  
BEGIN
INSERT INTO
    supply_other_info_ls (
        phid,
        mstphid,
        supply_ex_type2,
        mdg_no,
        sap_no,
        cgzz,
        fktj_no,
        cg_order_hb,
        incoterms_part1,
        incoterms_part2,
        cgdj,
        cgdj_reason,
        dj_dt,
        dj_process,
        auto_cgdd,
        qr_control,
        fpyz,
        factory,
        ent_no,
        account,
        fkfsqd,
        ent_gzdj,
        gzdj_reason,
        fkdj_no,
        fkdj_reason,
        sfqs,
        xh,
        plan_group,
        remarks,
        sfxyht,
        sbfk_dt,
        sbclfs,
        zzsfpxe,
        sfysfk,
        jcylzd,
        ddjsylzd,
        zgpl,
        rzywlb,
        rzcpx,
        gh_stat,
        supply_attribute,
        ycsupply_no,
        partner_gn,
        yw_partner_js,
        other_supply,
        default_partner,
        creator,
        editor,
        ng_insert_dt,
        ng_update_dt
    )
SELECT
    x.phid,
    x.mstphid,
    x.supply_ex_type2,
    x.mdg_no,
    x.sap_no,
    x.cgzz,
    x.fktj_no,
    x.cg_order_hb,
    x.incoterms_part1,
    x.incoterms_part2,
    x.cgdj,
    x.cgdj_reason,
    x.dj_dt,
    x.dj_process,
    x.auto_cgdd,
    x.qr_control,
    x.fpyz,
    x.factory,
    x.ent_no,
    x.account,
    x.fkfsqd,
    x.ent_gzdj,
    x.gzdj_reason,
    x.fkdj_no,
    x.fkdj_reason,
    x.sfqs,
    x.xh,
    x.plan_group,
    x.remarks,
    x.sfxyht,
    x.sbfk_dt,
    x.sbclfs,
    x.zzsfpxe,
    x.sfysfk,
    x.jcylzd,
    x.ddjsylzd,
    x.zgpl,
    x.rzywlb,
    x.rzcpx,
    x.gh_stat,
    x.supply_attribute,
    x.ycsupply_no,
    x.partner_gn,
    x.yw_partner_js,
    x.other_supply,
    x.default_partner,
    x.creator,
    x.editor,
    x.ng_insert_dt,
    x.ng_update_dt
FROM
    XMLTABLE (
        '/supply_other_info/table/newRow/row' PASSING XMLTYPE (xmlsource) COLUMNS phid NUMBER (26, 0) PATH 'phid',
        mstphid NUMBER (25) PATH 'mstphid',
        supply_ex_type2 VARCHAR2 (100) PATH 'supply_ex_type2',
        mdg_no VARCHAR2 (100) PATH 'mdg_no',
        sap_no VARCHAR2 (100) PATH 'sap_no',
        cgzz VARCHAR2 (100) PATH 'cgzz',
        fktj_no VARCHAR2 (100) PATH 'fktj_no',
        cg_order_hb VARCHAR2 (100) PATH 'cg_order_hb',
        incoterms_part1 VARCHAR2 (100) PATH 'incoterms_part1',
        incoterms_part2 VARCHAR2 (100) PATH 'incoterms_part2',
        cgdj VARCHAR2 (100) PATH 'cgdj',
        cgdj_reason VARCHAR2 (100) PATH 'cgdj_reason',
        dj_dt VARCHAR2 (100) PATH 'dj_dt',
        dj_process VARCHAR2 (100) PATH 'dj_process',
        auto_cgdd VARCHAR2 (100) PATH 'auto_cgdd',
        qr_control VARCHAR2 (100) PATH 'qr_control',
        fpyz VARCHAR2 (100) PATH 'fpyz',
        factory VARCHAR2 (100) PATH 'factory',
        ent_no VARCHAR2 (100) PATH 'ent_no',
        account VARCHAR2 (100) PATH 'account',
        fkfsqd VARCHAR2 (100) PATH 'fkfsqd',
        ent_gzdj VARCHAR2 (100) PATH 'ent_gzdj',
        gzdj_reason VARCHAR2 (100) PATH 'gzdj_reason',
        fkdj_no VARCHAR2 (100) PATH 'fkdj_no',
        fkdj_reason VARCHAR2 (100) PATH 'fkdj_reason',
        sfqs VARCHAR2 (100) PATH 'sfqs',
        xh VARCHAR2 (100) PATH 'xh',
        plan_group VARCHAR2 (100) PATH 'plan_group',
        remarks VARCHAR2 (100) PATH 'remarks',
        sfxyht VARCHAR2 (100) PATH 'sfxyht',
        sbfk_dt VARCHAR2 (100) PATH 'sbfk_dt',
        sbclfs VARCHAR2 (100) PATH 'sbclfs',
        zzsfpxe VARCHAR2 (100) PATH 'zzsfpxe',
        sfysfk VARCHAR2 (100) PATH 'sfysfk',
        jcylzd VARCHAR2 (100) PATH 'jcylzd',
        ddjsylzd VARCHAR2 (100) PATH 'ddjsylzd',
        zgpl VARCHAR2 (100) PATH 'zgpl',
        rzywlb VARCHAR2 (100) PATH 'rzywlb',
        rzcpx VARCHAR2 (100) PATH 'rzcpx',
        gh_stat VARCHAR2 (100) PATH 'gh_stat',
        supply_attribute VARCHAR2 (100) PATH 'supply_attribute',
        ycsupply_no VARCHAR2 (100) PATH 'ycsupply_no',
        partner_gn VARCHAR2 (100) PATH 'partner_gn',
        yw_partner_js VARCHAR2 (100) PATH 'yw_partner_js',
        other_supply VARCHAR2 (100) PATH 'other_supply',
        default_partner VARCHAR2 (100) PATH 'default_partner',
        creator NUMBER (25) PATH 'creator',
        editor NUMBER (25) PATH 'editor',
        ng_insert_dt DATE PATH 'ng_insert_dt',
        ng_update_dt DATE PATH 'ng_update_dt'
    ) x;

SELECT
    COUNT(*) INTO icount
FROM
    supply_other_info_ls;

IF icount > 0 THEN
UPDATE supply_other_info_ls
SET
    mstphid = pphid;

DELETE FROM supply_other_info
WHERE
    mstphid = pphid;

INSERT INTO
    supply_other_info
SELECT
    *
FROM
    supply_other_info_ls;

DELETE FROM supply_other_info_ls;

END IF;

EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20001, 'Error processing new rows: ' || SQLERRM);

END;

-- 修改行  
BEGIN
INSERT INTO
    supply_other_info_ls (
        phid,
        mstphid,
        supply_ex_type2,
        mdg_no,
        sap_no,
        cgzz,
        fktj_no,
        cg_order_hb,
        incoterms_part1,
        incoterms_part2,
        cgdj,
        cgdj_reason,
        dj_dt,
        dj_process,
        auto_cgdd,
        qr_control,
        fpyz,
        factory,
        ent_no,
        account,
        fkfsqd,
        ent_gzdj,
        gzdj_reason,
        fkdj_no,
        fkdj_reason,
        sfqs,
        xh,
        plan_group,
        remarks,
        sfxyht,
        sbfk_dt,
        sbclfs,
        zzsfpxe,
        sfysfk,
        jcylzd,
        ddjsylzd,
        zgpl,
        rzywlb,
        rzcpx,
        gh_stat,
        supply_attribute,
        ycsupply_no,
        partner_gn,
        yw_partner_js,
        other_supply,
        default_partner,
        creator,
        editor,
        ng_insert_dt,
        ng_update_dt
    )
SELECT
    x.phid,
    x.mstphid,
    x.supply_ex_type2,
    x.mdg_no,
    x.sap_no,
    x.cgzz,
    x.fktj_no,
    x.cg_order_hb,
    x.incoterms_part1,
    x.incoterms_part2,
    x.cgdj,
    x.cgdj_reason,
    x.dj_dt,
    x.dj_process,
    x.auto_cgdd,
    x.qr_control,
    x.fpyz,
    x.factory,
    x.ent_no,
    x.account,
    x.fkfsqd,
    x.ent_gzdj,
    x.gzdj_reason,
    x.fkdj_no,
    x.fkdj_reason,
    x.sfqs,
    x.xh,
    x.plan_group,
    x.remarks,
    x.sfxyht,
    x.sbfk_dt,
    x.sbclfs,
    x.zzsfpxe,
    x.sfysfk,
    x.jcylzd,
    x.ddjsylzd,
    x.zgpl,
    x.rzywlb,
    x.rzcpx,
    x.gh_stat,
    x.supply_attribute,
    x.ycsupply_no,
    x.partner_gn,
    x.yw_partner_js,
    x.other_supply,
    x.default_partner,
    x.creator,
    x.editor,
    x.ng_insert_dt,
    x.ng_update_dt
FROM
    XMLTABLE (
        '/supply_other_info/table/modifiedRow/row' PASSING XMLTYPE (xmlsource) COLUMNS phid NUMBER (26, 0) PATH 'phid',
        mstphid NUMBER (25) PATH 'mstphid',
        supply_ex_type2 VARCHAR2 (100) PATH 'supply_ex_type2',
        mdg_no VARCHAR2 (100) PATH 'mdg_no',
        sap_no VARCHAR2 (100) PATH 'sap_no',
        cgzz VARCHAR2 (100) PATH 'cgzz',
        fktj_no VARCHAR2 (100) PATH 'fktj_no',
        cg_order_hb VARCHAR2 (100) PATH 'cg_order_hb',
        incoterms_part1 VARCHAR2 (100) PATH 'incoterms_part1',
        incoterms_part2 VARCHAR2 (100) PATH 'incoterms_part2',
        cgdj VARCHAR2 (100) PATH 'cgdj',
        cgdj_reason VARCHAR2 (100) PATH 'cgdj_reason',
        dj_dt VARCHAR2 (100) PATH 'dj_dt',
        dj_process VARCHAR2 (100) PATH 'dj_process',
        auto_cgdd VARCHAR2 (100) PATH 'auto_cgdd',
        qr_control VARCHAR2 (100) PATH 'qr_control',
        fpyz VARCHAR2 (100) PATH 'fpyz',
        factory VARCHAR2 (100) PATH 'factory',
        ent_no VARCHAR2 (100) PATH 'ent_no',
        account VARCHAR2 (100) PATH 'account',
        fkfsqd VARCHAR2 (100) PATH 'fkfsqd',
        ent_gzdj VARCHAR2 (100) PATH 'ent_gzdj',
        gzdj_reason VARCHAR2 (100) PATH 'gzdj_reason',
        fkdj_no VARCHAR2 (100) PATH 'fkdj_no',
        fkdj_reason VARCHAR2 (100) PATH 'fkdj_reason',
        sfqs VARCHAR2 (100) PATH 'sfqs',
        xh VARCHAR2 (100) PATH 'xh',
        plan_group VARCHAR2 (100) PATH 'plan_group',
        remarks VARCHAR2 (100) PATH 'remarks',
        sfxyht VARCHAR2 (100) PATH 'sfxyht',
        sbfk_dt VARCHAR2 (100) PATH 'sbfk_dt',
        sbclfs VARCHAR2 (100) PATH 'sbclfs',
        zzsfpxe VARCHAR2 (100) PATH 'zzsfpxe',
        sfysfk VARCHAR2 (100) PATH 'sfysfk',
        jcylzd VARCHAR2 (100) PATH 'jcylzd',
        ddjsylzd VARCHAR2 (100) PATH 'ddjsylzd',
        zgpl VARCHAR2 (100) PATH 'zgpl',
        rzywlb VARCHAR2 (100) PATH 'rzywlb',
        rzcpx VARCHAR2 (100) PATH 'rzcpx',
        gh_stat VARCHAR2 (100) PATH 'gh_stat',
        supply_attribute VARCHAR2 (100) PATH 'supply_attribute',
        ycsupply_no VARCHAR2 (100) PATH 'ycsupply_no',
        partner_gn VARCHAR2 (100) PATH 'partner_gn',
        yw_partner_js VARCHAR2 (100) PATH 'yw_partner_js',
        other_supply VARCHAR2 (100) PATH 'other_supply',
        default_partner VARCHAR2 (100) PATH 'default_partner',
        creator NUMBER (25) PATH 'creator',
        editor NUMBER (25) PATH 'editor',
        ng_insert_dt DATE PATH 'ng_insert_dt',
        ng_update_dt DATE PATH 'ng_update_dt'
    ) x;

SELECT
    COUNT(*) INTO icount
FROM
    supply_other_info_ls;

IF icount > 0 THEN
UPDATE supply_other_info_ls
SET
    mstphid = pphid;

MERGE INTO supply_other_info p USING supply_other_info_ls l ON (p.phid = l.phid) WHEN MATCHED THEN
UPDATE
SET
    p.mstphid = l.mstphid,
    p.supply_ex_type2 = l.supply_ex_type2,
    p.mdg_no = l.mdg_no,
    p.sap_no = l.sap_no,
    p.cgzz = l.cgzz,
    p.fktj_no = l.fktj_no,
    p.cg_order_hb = l.cg_order_hb,
    p.incoterms_part1 = l.incoterms_part1,
    p.incoterms_part2 = l.incoterms_part2,
    p.cgdj = l.cgdj,
    p.cgdj_reason = l.cgdj_reason,
    p.dj_dt = l.dj_dt,
    p.dj_process = l.dj_process,
    p.auto_cgdd = l.auto_cgdd,
    p.qr_control = l.qr_control,
    p.fpyz = l.fpyz,
    p.factory = l.factory,
    p.ent_no = l.ent_no,
    p.account = l.account,
    p.fkfsqd = l.fkfsqd,
    p.ent_gzdj = l.ent_gzdj,
    p.gzdj_reason = l.gzdj_reason,
    p.fkdj_no = l.fkdj_no,
    p.fkdj_reason = l.fkdj_reason,
    p.sfqs = l.sfqs,
    p.xh = l.xh,
    p.plan_group = l.plan_group,
    p.remarks = l.remarks,
    p.sfxyht = l.sfxyht,
    p.sbfk_dt = l.sbfk_dt,
    p.sbclfs = l.sbclfs,
    p.zzsfpxe = l.zzsfpxe,
    p.sfysfk = l.sfysfk,
    p.jcylzd = l.jcylzd,
    p.ddjsylzd = l.ddjsylzd,
    p.zgpl = l.zgpl,
    p.rzywlb = l.rzywlb,
    p.rzcpx = l.rzcpx,
    p.gh_stat = l.gh_stat,
    p.supply_attribute = l.supply_attribute,
    p.ycsupply_no = l.ycsupply_no,
    p.partner_gn = l.partner_gn,
    p.yw_partner_js = l.yw_partner_js,
    p.other_supply = l.other_supply,
    p.default_partner = l.default_partner,
    p.creator = l.creator,
    p.editor = l.editor,
    p.ng_insert_dt = l.ng_insert_dt,
    p.ng_update_dt = l.ng_update_dt
DELETE FROM supply_other_info_ls;

END IF;

EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (
    -20002,
    'Error processing modified rows: ' || SQLERRM
);

END;

-- 删除行  
BEGIN
INSERT INTO
    supply_other_info_ls (phid)
SELECT
    x.phid
FROM
    XMLTABLE (
        '/supply_other_info/table/deletedRow/row' PASSING XMLTYPE (xmlsource) COLUMNS phid NUMBER (26) PATH 'key'
    ) x;

SELECT
    COUNT(*) INTO icount
FROM
    supply_other_info_ls;

IF icount > 0 THEN
DELETE FROM supply_other_info
WHERE
    phid IN (
        SELECT
            phid
        FROM
            supply_other_info_ls
    );

DELETE FROM supply_other_info_ls;

END IF;

EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (
    -20003,
    'Error processing deleted rows: ' || SQLERRM
);

END;

-- 始终清空临时表（如果需要在每个块之后清空，可以保留在每个END;之后）  
-- DELETE FROM supply_other_info_ls; -- 已在每个块中清空，这里可以省略  
EXCEPTION WHEN e_invalid_xml THEN RAISE_APPLICATION_ERROR (-20000, 'Invalid XML input.');

WHEN OTHERS THEN RAISE_APPLICATION_ERROR (
    -20004,
    'An unexpected error occurred: ' || SQLERRM
);

END;